﻿/*
* 修改表结构
*/

if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_Customer' and B.name = 'CustomerTypeId'
)
 begin
	print '字段【CustomerTypeId】已存在'
 end
else
 begin
	alter table T_Customer add CustomerTypeId int default 0
 end

if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_Customer' and B.name = 'Gender'
)
 begin
	print '字段【Gender】已存在'
 end
else
 begin
	alter table T_Customer add Gender int default 1

 end

go
update T_Customer set Gender=1

if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_Customer' and B.name = 'Wangwang'
)
 begin
	print '字段【Wangwang】已存在'
 end
else
 begin
	alter table T_Customer add Wangwang varchar(100) default ''
 end


if exists(
	select 1 from sys.all_objects A,sys.all_columns B
	where A.object_id=B.object_id and A.name='T_Classification' and B.name = 'Description'
)
 begin
	print '字段【T_Classification】已存在'
 end
else
 begin
	alter table T_Classification add [Description] varchar(1000) default ''
 end



GO
/*
* 新建视图
*/
if exists (select * from sysobjects where id = object_id(N'View_T_Customer') and OBJECTPROPERTY(id, N'IsView') = 1)
	drop view [dbo].[View_T_Customer]

GO

CREATE VIEW [dbo].[View_T_Customer]
AS 
SELECT 
T.* 
,CreateUserName=CTUser.UserName
,UpdateUserName=UTUser.UserName
,CustomerTypeName=CustomerType.ClassificationName
FROM T_Customer T
left outer join Sys_User as CTUser on T.CreateUserId=CTUser.UserId
left outer join Sys_User as UTUser on T.CreateUserId=UTUser.UserId
left outer join T_Classification as CustomerType 
	on ParentId=(select top 1 ClassificationId from T_Classification where ClassificationCode='CustomerType')
	and CustomerType.ClassificationId = T.CustomerTypeId 
GO

/*----------------------------------------------
添加类型
----------------------------------------------*/
if exists(select 1 from T_Classification where ClassificationCode='CustomerType')
 begin
    print '【客户类型】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'CustomerType','客户类型')
 end



/*
* 添加菜单
*/
declare @NewModuleId int;
select @NewModuleId=ModuleId from Sys_Module where [ModuleCode]='003'

if not exists(select 1 from Sys_Module where [ModuleCode]='003004')
 begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('客户类型管理','003004',@NewModuleId,1,'/Phone/Classification.aspx?Code=CustomerType',1,'06','','001')
 end
else
 begin
	print '【供应商资料录入】菜单模块已存在'
 end


